package dao;

import java.sql.*;
import java.util.ArrayList;
import java.util.Hashtable;

import orm.Teacher;
import orm.Student;
import util.DBHelper;

public class StudentDAO {
	Connection conn = DBHelper.getConnection();
	Statement stmt;
	ResultSet rs;
	
	public Hashtable<Integer, String> getStudentsByTeacherID(int teacherID) throws SQLException {
		Hashtable<Integer, String> ht = new Hashtable<Integer, String>();
		stmt = conn.createStatement();
		rs = stmt.executeQuery("select stu.StudentId, stu.SName ,stu.ClassID " +
				"from (select ct.ClassID as c from classteacher ct where ct.TeacherID = " + teacherID + ") as cs " +
				"inner join student as stu ON cs.c = stu.ClassID; " );
				
		
		while (rs.next()) {
			ht.put(rs.getInt(1), rs.getString(2));
		}
		
		return ht;
	}
	
	public Hashtable<Integer, String> getStudentsByIndiTeacherID(int teacherID) throws SQLException {
		Hashtable<Integer, String> ht = new Hashtable<Integer, String>();
		stmt = conn.createStatement();
		rs = stmt.executeQuery("SELECT stu.StudentId, stu.SName FROM student as stu inner JOIN ( " +
				"SELECT distinct ds.StudentID as sid from dxstudent as ds inner join ( " +
				"select ic.IClassID as icid from individualclass ic where ic.TeacherID = " + teacherID + 
				")as icids on ds.IClassID = icids.icid " +
				") as sids ON stu.StudentId = sids.sid ");
				

		while (rs.next()) {
			ht.put(rs.getInt(1), rs.getString(2));
		}
		
		return ht;
	}
	
	public Student getStudentID(int studentID) throws SQLException {
		Student student = new Student();
		stmt = conn.createStatement();
		rs = stmt.executeQuery("select * from student where StudentID =" + studentID + "");
		while (rs.next()) {
			int i = 1;
			student.setStudentID(rs.getInt(i++));
			student.setStudentName(rs.getString(i++));
			student.setSex(rs.getInt(i++));
			student.setClassID(rs.getString(i++));
			student.setClassName(rs.getString(i++));
			student.setIClassID(rs.getString(i++));
			student.setIClassName(rs.getString(i++));
			student.setBirthday(rs.getString(i++));
			student.setAge(rs.getInt(i++));
			student.setLeftear(rs.getInt(i++));
			student.setRightear(rs.getInt(i++));
			student.setHearingEvaluation(rs.getString(i++));
			student.setLanguageEvaluation(rs.getString(i++));
			student.setLearningEvaluation(rs.getString(i++));
			student.setGriffithsEvaluation(rs.getString(i++));
			student.setGuardian(rs.getString(i++));
			student.setTelephone(rs.getString(i++));
			student.setAllocation(rs.getInt(i++));
			student.setRemark(rs.getString(i++));
			
		}
		
		return student;
	}
	
	public void insertStudent(Student student) throws SQLException {
		stmt = conn.createStatement();
		stmt.execute("insert into student values('" + student.getStudentID() + "', '" + 
				student.getStudentName() + "', " + student.getSex() + ", '" + student.getClassID() + 
				"', '" + student.getClassName() + "', '" + student.getIClassID() + "', '" + student.getIClassName() + "', '" + 
				student.getBirthday() + "', " + student.getAge() + ", " +
				student.getLeftear() + ", " + student.getRightear() + ", '" +
				student.getHearingEvaluation() + "', '" + student.getLanguageEvaluation() + "', '" +
				student.getLearningEvaluation() + "', '" + student.getGriffithsEvaluation() + "', '" + 
				student.getGuardian() + "', '" + student.getTelephone() + "', " +
				student.getAllocation() +  ",'" + student.getRemark() + "')");
		
		stmt.close();
		conn.close();
	}
	
	public void updateStudent(Statement statement, Student student) throws SQLException {
		statement.execute("update Student set studentName = '" + 
				student.getStudentName() + "', Sex =" + student.getSex() + 
				", ClassID = '" + student.getClassID() + "', ClassName = '" + student.getClassName() + 
				"', IClassID = '" + student.getIClassID() + "', IClassName = '" + student.getIClassName() + 
				"', Birthday = '" + student.getBirthday() + "', Age = " + student.getAge() + 
				", Leftear = " + student.getLeftear() + ", Rightear = " + 
				student.getRightear() + ", HearingEvaluation = '" + student.getHearingEvaluation() + 
				"', LanguageEvaluation = '" + student.getLanguageEvaluation() + "', LearningEvaluation = '" + 
				student.getLearningEvaluation() + "', GriffithsEvaluation = '" + student.getGriffithsEvaluation() + 
				"', Guardian = '" + student.getGuardian() + "', Telephone = '" + student.getTelephone() + 
				"', Allocation = " + student.getAllocation() + ", Remark= '" + student.getRemark() + 
				"' where StudentID = '" + student.getStudentID() + "'");
	}
	
	public void deleteStudent(Statement statement, String studentID) throws SQLException {
		statement.execute("delete from student where StudentID = '" + studentID + "'");
	}
	
	public ArrayList<Student> getAllStudents() throws SQLException {
		ArrayList<Student> al = new ArrayList<Student>();
		conn = DBHelper.getConnection();
		stmt = conn.createStatement();

		rs = stmt.executeQuery("select StudentID, StudentName, Sex, Age, ClassID, Guardian, Allocation from student ORDER BY StudentID ASC");
		Student student = null;
		
		while (rs.next()) {
			student = new Student();
			student.setStudentID(rs.getInt("StudentID"));
			student.setStudentName(rs.getString("StudentName"));
			student.setSex(rs.getInt("Sex"));
			student.setAge(rs.getInt("Age"));
			student.setClassID(rs.getString("ClassID"));
			student.setGuardian(rs.getString("Guardian"));
			student.setAllocation(rs.getInt("Allocation"));
			
			al.add(student);
		}
		
		stmt.close();
		conn.close();
		
		return al;
	}
	
	public boolean doesExist(int studentID) throws SQLException {
		stmt = conn.createStatement();
		rs = stmt.executeQuery("select count(*) from student where StudentID=" + studentID + "");
		rs.next();
		return rs.getInt(1) == 0 ? false : true;
	}
}
